/*
* $Id: to_0_2_0.cpp 302 2011-04-08 15:18:13Z volok-aleksej@yandex.ru $
*
* Copyright 2009 by Volokitin Alexey
* All rights reserved
*
*/
#include "Common/include.h"
#include "Database/Database.h"
#include "Database/Tables/TableNames.h"

void to_0_2_0(CDatabase* pDb){
	std::tstring strRequest;
	//update families table
	if(!pDb->IsExistTable(FAMILIES_TABLE_NAME)){
		strRequest = _T("CREATE TABLE IF NOT EXISTS ");
		pDb->ExecRequest(strRequest + FAMILIES_TABLE_NAME + _T("(id_family INTEGER NOT NULL, name TEXT NOT NULL, ")\
			_T("id_point INTEGER NOT NULL, type INTEGER NULL, note TEXT NULL, id_record INTEGER NULL, ")\
			_T("PRIMARY KEY(id_family ASC AUTOINCREMENT), CONSTRAINT fkFamiliesPoint ") \
			_T("FOREIGN KEY(id_point) REFERENCES points(id_point),")\
			_T("CONSTRAINT fkFamiliesRecord FOREIGN KEY(id_record) REFERENCES field_values(id_record) ") \
			_T("ON DELETE SET NULL ON UPDATE SET NULL);"));
	} else {
		pDb->ExecRequest(_T("CREATE TEMPORARY TABLE TEMP_TABLE(id_family INTEGER PRIMARY KEY,")\
						_T("name TEXT, id_point INTEGER, type INTEGER, note TEXT, id_record INTEGER);"));
		strRequest = _T("INSERT INTO TEMP_TABLE SELECT id_family, name, id_point, type, note, id_record FROM ");
		pDb->ExecRequest(strRequest + FAMILIES_TABLE_NAME);
		strRequest = _T("DROP TABLE ");
		pDb->ExecRequest(strRequest + FAMILIES_TABLE_NAME);
		strRequest = _T("CREATE TABLE IF NOT EXISTS ");
		pDb->ExecRequest(strRequest + FAMILIES_TABLE_NAME + _T("(id_family INTEGER NOT NULL, name TEXT NOT NULL, ")\
						_T("id_point INTEGER NOT NULL, type INTEGER NULL, note TEXT NULL, id_record INTEGER NULL, ")\
						_T("PRIMARY KEY(id_family ASC AUTOINCREMENT), CONSTRAINT fkFamiliesPoint ") \
						_T("FOREIGN KEY(id_point) REFERENCES points(id_point),")\
						_T("CONSTRAINT fkFamiliesRecord FOREIGN KEY(id_record) REFERENCES field_values(id_record) ") \
						_T("ON DELETE SET NULL ON UPDATE SET NULL);"));
		strRequest = _T("INSERT INTO ");
		pDb->ExecRequest(strRequest + FAMILIES_TABLE_NAME + _T("(id_family, name, id_point, type, note, id_record) ")\
						_T("SELECT id_family, name, id_point, type, note, id_record FROM TEMP_TABLE;"));
		pDb->ExecRequest(_T("DROP TABLE TEMP_TABLE;"));
	}
//update queen bees table
	if(pDb->IsExistTable(QUEEN_BEE_TABLE_NAME)){
		strRequest = _T("DROP TABLE ");
		pDb->ExecRequest(strRequest + QUEEN_BEE_TABLE_NAME);
	}
	strRequest = _T("CREATE TABLE IF NOT EXISTS ");
	pDb->ExecRequest(strRequest + QUEEN_BEE_TABLE_NAME + _T("(id_queen_bee INTEGER NOT NULL, name TEXT NOT NULL,")\
					_T("description TEXT NULL, id_family INTEGER NULL, id_parent_queen_bee INTEGER NULL,")\
					_T(" date DATE NOT NULL, breed INTEGER NULL, fert TEXT NULL, is_dead BOOL,")\
					_T("PRIMARY KEY(id_queen_bee ASC AUTOINCREMENT), CONSTRAINT ftFamily FOREIGN KEY(id_family)")\
					_T(" REFERENCES family(id_family) ON DELETE SET NULL, CONSTRAINT ftQueenBee FOREIGN KEY(id_parent_queen_bee)")\
					_T(" REFERENCES queen_bee(id_queen_bee));"));
}